package com.campus_information_portal.dao;

import com.campus_information_portal.entity.MarkCondition;
import com.campus_information_portal.entity.Student;
import com.campus_information_portal.entity.StudentInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @author yyou
 * @description studentDAO
 * @project_name campus_information_portal
 * @package com.campus_information_portal.dao
 * @create 2018-04-19 9:53
 **/
public interface StudentDao {
    /**
     * @param stu_num
     * @Description 根据Student表中的学生学号查询学生个人信息
     * @author 20155883 万珊
     * @methodName queryStudentByStuNum
     * @returnType Student
     * @create 2018/4/26 8:45
     **/
    @Select("select s.stu_info_id,s.stu_num,s.stu_name,s.stu_sex,s.stu_idcard_num,s.stu_birthday,s.stu_nation,s.stu_poli,s.stu_phone,s.stu_nativeplace,s.stu_password,s.stu_grade," +
            "c.cet_4_grade,c.cet_6_grade," +
            "a.academy_name" +
            " from student s,academy a,cet_46 c" +
            " where s.stu_num=#{stu_num} and s.academy_id=a.academy_id and s.cet_46_id=c.cet_46_id")
    StudentInfo queryStudentByStuNum(@Param("stu_num") int stu_num);

    /**
     * @param stu_num
     * @Description 查询学号是否存在
     * @author 20155880 房雁颖
     * @methodName queryStuNum
     * @returnType int
     * @create 2018/4/26 8:45
     **/
    @Select("select * from student where stu_num=#{stu_num}")
    Student queryStuNum(@Param("stu_num") int stu_num);

    /**
     * @param student
     * @Description 增加一个学生（学号+密码）
     * @author 20155880 房雁颖
     * @methodName addStudent
     * @returnType boolean
     * @create 2018/4/26 15:26
     **/
    @Insert("insert into student(stu_num,stu_password) values(#{student.stuNum},#{student.stuPassword})")
    boolean addStudent(@Param("student") Student student, @Param("id") int id);

    /**
     * @param student
     * @Description 修改学生信息表（姓名，性别，身份证，出生年月，民族，电话，籍贯）
     * @author 20155880 房雁颖
     * @methodName updateStudent
     * @returnType boolean
     * @create 2018/4/26 15:26
     **/
    @Update("update student set stu_name=#{student.stuName},stu_sex=#{student.stuSex},stu_idcard_num=#{student.stuIdcardNum},stu_birthday=#{student.stuBirthday},stu_nation=#{student.stuNation},stu_phone=#{student.stuPhone},stu_nativeplace=#{student.stuNativeplace} where stu_num=#{student.stuNum}")
    boolean updateStudent(@Param("student") Student student);

    /**
     * @param stu_num
     * @Description 删除学生信息表【警告！这是真删！无特殊情况不可调用此接口！】
     * @author 20155883 万珊
     * @methodName deleteStudent
     * @returnType boolean
     * @create 2018/4/26 15:26
     **/
    @Delete("delete from student where stu_num=#{stu_num}")
    boolean deleteStudent(@Param("stu_num") int stu_num);

    /**
     * @Description 查询全部学生
     * @author 20155883 万珊
     * @methodName queryStudent
     * @returnType List<Student>
     * @create 2018/4/26 8:45
     **/
    @Select("select * from student")
    List<Student> queryStudent();

    /**
     * @Description 查询全部年级（除去重复字段）
     * @author 20155883 万珊
     * @methodName queryGrade
     * @returnType List
     * @create 2018/4/26 8:45
     **/
    @Select("select distinct stu_grade from student order by stu_grade desc")
    List<String> queryGrade();

    /**
     * @param
     * @Description 跟据学号查学生id
     * @author 20155884 王晓阳
     * @methodName
     * @returnType
     * @create 2018/5/27 14:13
     **/
    @Select("select stu_info_id from student where stu_num=#{stuNum}")
    Student queryStuIdByStuNum(@Param("stuNum") int stuNum);

    /**
     * @param
     * @Description 根据学生id查找学生信息
     * @author 20155884 王晓阳
     * @methodName
     * @returnType
     * @create 2018/5/27 23:19
     **/
    @Select("select * from student where stu_info_id=#{stuInfoId}")
    Student queryStudentByStuId(@Param("stuInfoId") int stuInfoId);

    /**
     * @param
     * @Description 查找所有学生id， 学号，姓名
     * @author 20155884 王晓阳
     * @methodName
     * @returnType
     * @create 2018/5/29 20:52
     **/
    @Select("select stu_info_id,stu_num,stu_name from student")
    List<MarkCondition> queryAllStudent();
}

